#!/usr/bin/python
# -*- coding: UTF-8 -*-

import time
import datetime
import mysql.connector
from  decimal import Decimal
from  decimal import getcontext

'''
获取数据乘以对应的权重系数，最后保存成每天的价格平均值
'''

# 创建链接数据库
config = {'host': '127.0.0.1',  # 默认127.0.0.1
          'user': 'root', 'password': '654321', 'port': 3306,  # 默认即为3306
          'database': 'worm_data', 'charset': 'utf8'  # 默认即为utf8
          }

config1 = {'host': 'localhost',  # 默认127.0.0.1
          'user': 'root', 'password': '123456', 'port': 3306,  # 默认即为3306
          'database': 'shopxxb2b2c', 'charset': 'utf8'  # 默认即为utf8
          }

config3 = {'host': 'rm-2zefd6473rz062234.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'prod_python', 'password': '939_58J6kAW)P&^', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }

#demical保留小数位数
getcontext().prec = 4


# 更新每天省市的平均值
def handle_everyday_data(type, num):

    price_date = getdate(num)
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config3)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = 'insert INTO province_avg_price(avg_price, type, product_name, price_date, province_name, created_date, last_modified_date, version) SELECT avg(price), type, name, price_date, province_name, NOW(), NOW(), 0  FROM shop_price_daily_info WHERE type= %s AND price_date = %s GROUP BY  province_name'
        cursor.execute(stmt, (type, price_date))
        count = cursor.rowcount
        cnn.commit()
        print("保存数据成功！保存记录数", count)

    except mysql.connector.Error as e:
        print('保存处理数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    print(re_date)
    return re_date

# 循环调用主方法
def work(types , numCount):
    for type in types:
        for i in range(numCount):
            handle_everyday_data(type, i + 1)

pork_type = 1
beef_type = 2
mutton_type = 3
fish_type = 4001

# 类型
arr_types = [pork_type, beef_type, mutton_type, fish_type]
# arr_types = [fish_type]

if __name__ == '__main__':
    # N天前至昨天所有的数据拿过来 1为前天到昨天
    num = 1
    work(arr_types, num)


        
        
